IF OBJECT_ID('ssf.cmd_dir') IS NOT NULL
	DROP PROCEDURE ssf.cmd_dir
GO

CREATE PROCEDURE ssf.cmd_dir
	@path NVARCHAR(2048)
	, @subdirectory_fl BIT = 0
	, @attributes NVARCHAR(16) = NULL
	, @timefield NVARCHAR(1) = NULL
	, @dir XML OUTPUT
AS

/*
<object_header>

<object_summary>
	<purpose><![CDATA[
Capture and parse output of DIR command.
Supported options include /S, /A, /T (from cmd.exe prompt, see DIR /?).
	]]></purpose>
</object_summary>

</object_header>
*/
SET NOCOUNT ON

-- ============
/*<code_comment><![CDATA[declare local variables]]></code_comment>*/
DECLARE @cmd NVARCHAR(2048)
-- ============

-- ============
/*<code_comment><![CDATA[intialize and evaluate parameters]]></code_comment>*/
SET @dir = NULL

IF ISNULL(@timefield, N'') NOT IN (N'', N'C', N'A', N'W')
BEGIN
	SET @timefield = N''
	RAISERROR(N'ignoring @timefield - must be C, A, or W', 10, 1)
END

IF NULLIF(@path, N'') IS NULL
BEGIN
	RAISERROR(N'@path cannot be null or blank', 16, 1)
	RETURN 1
END

-- TODO: validate @path is just a path or pattern
-- ============

-- ============
/*<code_comment><![CDATA[run DIR command with xp_cmdshell and capture output]]></code_comment>*/
IF OBJECT_ID('tempdb..#cmd') IS NOT NULL DROP TABLE #cmd
CREATE TABLE #cmd (
	line_number INT IDENTITY(1, 1) NOT NULL
	, line VARCHAR(2048) NULL
);

SET @cmd = N'DIR'
	+ SPACE(1) + N'/-C'
	+ CASE WHEN @subdirectory_fl = 1
		THEN SPACE(1) + N'/S'
		ELSE N''
		END
	+ CASE WHEN NULLIF(@attributes , N'') IS NOT NULL
		THEN SPACE(1) + N'/A' + @attributes
		ELSE N''
		END
	+ CASE WHEN NULLIF(@timefield , N'') IS NOT NULL
		THEN SPACE(1) + N'/T' + @timefield
		ELSE N''
		END
	+ SPACE(1) + @path

PRINT(@cmd)

INSERT INTO #cmd (line)
EXECUTE master.dbo.xp_cmdshell @cmd
;

-- TODO: check for error message
-- ============

-- ============
/*<code_comment><![CDATA[cleanse output and drop NULL lines]]></code_comment>*/
IF OBJECT_ID('tempdb..#dir') IS NOT NULL DROP TABLE #dir
CREATE TABLE #dir (
	line_number INT NOT NULL
	, line VARCHAR(2048) NOT NULL
);

INSERT INTO #dir (line_number, line)
SELECT line_number, LTRIM(line) FROM #cmd WHERE line IS NOT NULL
;

CREATE UNIQUE CLUSTERED INDEX PK ON #dir (line_number)
;
-- ============

-- ============
/*<code_comment><![CDATA[parse and format output of DIR]]></code_comment>*/
;
WITH
directory AS (
-- the directory header message
	SELECT line_number
		, SUBSTRING(line, 14, 2048) AS name
	FROM #dir
	WHERE line LIKE N'Directory of %'
)
, directory_line_link AS (
-- identify link from valid content lines to their directory
	SELECT line_number
		, SUBSTRING(line, 1, 20) AS timefield
		, LTRIM(SUBSTRING(line, 21, 2048)) AS line
		, (SELECT MAX(line_number) FROM directory WHERE line_number < r.line_number) AS content_line_number
	FROM #dir r
	WHERE line LIKE N'[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]  [0-9][0-9]:[0-9][0-9] [AP]M %'
)
, content_line AS (
-- add directory to content lines
	SELECT d.name AS directory
		, c.timefield
		, c.line
	FROM directory_line_link c
	JOIN directory d
	ON c.content_line_number = d.line_number
)
, content_line_split AS (
-- identify split between DIR/size and name
	SELECT *
		, CASE WHEN line LIKE N'<DIR>%'
			THEN 6
			WHEN line LIKE N'[0-9]%'
			THEN PATINDEX(N'%[^0-9]%', line)
			ELSE NULL
			END AS split_index
	FROM content_line
)
, content AS (
-- parse DIR/size and name, format each field
	SELECT CAST(directory AS VARCHAR(2048)) AS directory
		, CAST(LTRIM(SUBSTRING(line, split_index, 2048)) AS VARCHAR(2048)) AS name
		, CAST(timefield AS DATETIME) AS timefield
		, CAST(CASE WHEN line LIKE N'<DIR>%'
			THEN 1
			ELSE 0
			END AS BIT) AS directory_fl
		, CAST(CASE WHEN line LIKE N'<DIR>%'
			THEN NULL
			ELSE RTRIM(SUBSTRING(line, 1, split_index))
			END AS BIGINT) AS size
	FROM content_line_split
)
SELECT @dir = (
-- convert to XML
	SELECT @path AS "@path"
		, @subdirectory_fl AS "@subdirectory_fl"
		, @attributes AS "@attributes"
		, @timefield AS "@timefield"
		, (
			SELECT directory AS "@name"
				, (
					SELECT name AS "@name"
						, timefield
						, directory_fl
						, size
					FROM content
					WHERE directory = c.directory
					FOR XML PATH(N'content'), TYPE
				)
			FROM content c
			GROUP BY directory
			FOR XML PATH(N'directory'), TYPE
		)
	FOR XML PATH(N'cmd_dir'), TYPE
)
-- ============

RETURN @@ERROR
GO
